﻿
CREATE VIEW [dbo].[fares_viewStocCurent]
AS
SELECT     s.IDArticol, s.Pret, s.IDVarinataArticol, s.IDDepartament, COALESCE (stin.Intrari, 0) AS Intrari, COALESCE (stout.Iesiri, 0) AS Iesiri, s.StocInitial, s.StocPrecedent, 
                      COALESCE (stin.Intrari, 0) - COALESCE (stout.Iesiri, 0) + s.StocInitial + s.StocPrecedent AS Stoc
FROM         dbo.Stocuri AS s LEFT OUTER JOIN
                          (SELECT     ddin.IDArticol, SUM(ddin.Cantitate) AS Intrari
                            FROM          dbo.DocumenteDetaliu AS ddin INNER JOIN
                                                   dbo.Documente AS din ON ddin.IDDocument = din.IDDocument INNER JOIN
                                                   dbo.TipDocument AS td ON din.IDTipDocument = td.IDTipDocument
                            WHERE      (td.ModificaStoc = 1) AND (td.TipOperatie LIKE '%I%')
                            GROUP BY ddin.IDArticol) AS stin ON stin.IDArticol = s.IDArticol LEFT OUTER JOIN
                          (SELECT     ddin.IDArticol, SUM(ddin.Cantitate) AS Iesiri
                            FROM          dbo.DocumenteDetaliu AS ddin INNER JOIN
                                                   dbo.Documente AS din ON ddin.IDDocument = din.IDDocument INNER JOIN
                                                   dbo.TipDocument AS td ON din.IDTipDocument = td.IDTipDocument
                            WHERE      (td.ModificaStoc = 1) AND (td.TipOperatie LIKE '%O%')
                            GROUP BY ddin.IDArticol) AS stout ON stout.IDArticol = s.IDArticol